/**
 *
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import metier.*;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import metier.Semestre;
/**
 * @author Quipu-Team
 *
 */
public class GroupeJDBC extends Groupe {
    private final static String NOM = "Nom_Groupe";
    private final static String NOMBREETUDIANTS = "Nombre_Etudiant";
    private final static String SEMESTRE = "Semestre";
    private final static String ID = "ID_GroupeEtudiant";
    private final static String NOMTABLE = "GroupeEtudiant";

    private final static String updateQuery = "update "
            + NOMTABLE + " set "
            + NOM + " = ?, "
            + SEMESTRE + " = ?, "
            + NOMBREETUDIANTS + " = ? where "
            +ID +" = ?";
    private final static String selectQuery = "select * from "
            + NOMTABLE;
    private final static String selectQuerySemestre = "select * from "
            + NOMTABLE + " where "
            +SEMESTRE + " = ?";
    private final static String loadQuery = "select * from " 
            +NOMTABLE + " where "
            +ID + " = ?";
    private final static String deleteQuery = "DELETE FROM " 
            +NOMTABLE + " WHERE "
            +ID + "= ?";
    private final static String idQuery = "SELECT seq_groupe.nextval  FROM dual";
    private final static String insertQuery = "insert into "+ NOMTABLE + " values(?, ?, ?, ?)";

    /**
     * @param nom
     * @param prenom
     * @param statut
     */
    public GroupeJDBC(String nom, int nombreEtudiants, Semestre semestre) {
        super(nom, nombreEtudiants, semestre);
    }

    public GroupeJDBC(){
        super();
    }

    protected int createId() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next()) {
                throw new ExceptionMetier("creating new id failed due to non generated id");
            }
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }

    @Override
    public void insert() throws ExceptionMetier {
        try {
			Connection con = ConnectionJDBC.getConnection();
			PreparedStatement insertEnseignant = con.prepareStatement(insertQuery);
                        if( getId() == 0) setId(createId());
			insertEnseignant.setInt(1, this.getId());
			insertEnseignant.setInt(2, this.getSemestre().getId());
			insertEnseignant.setString(3, this.getNom());
			insertEnseignant.setInt(4, this.getNombreEtudiants());
			insertEnseignant.executeQuery();
        }
        catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
        catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
    }

    /**
     * M�thode qui permet de mettre � jour l'objet dans le syst�me de stockage
     */
    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement update = con.prepareStatement(updateQuery);
            update.setString(1, getNom());
            update.setInt(2, getSemestre().getId());
            update.setInt(3, getNombreEtudiants());
            update.setInt(4, getId());
            update.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }

    /**
     * M�thode qui permet de supprimer l'objet dans le syst�me de stockage
     */
    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement sta = con.prepareStatement(deleteQuery);
            sta.setInt(1, getId());
            sta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    /**
     * M�thode qui permet d'obtenir la liste
     */
    public static ArrayList<ElementListe> select() throws ExceptionMetier {
        try {
            ArrayList<ElementListe> list = new ArrayList<ElementListe>();
            ResultSet rset = null;
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement();
            rset = sta.executeQuery(selectQuery);
            while (rset.next()) {

            	
                Semestre sem = new SemestreJDBC("",null,null,null);
                sem.load(rset.getInt(SEMESTRE));
                String nom = rset.getString(NOM);
                int nbe = rset.getInt(NOMBREETUDIANTS);
                int id = rset.getInt(ID);
                String nomSemestre = sem.getNom();
            	           	
                ElementListe e = new ElementListe(id, nom + ", "+ nbe +", " +  nomSemestre);
                list.add(e);
            }
            rset.close();
            sta.close();
            return list;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }

    public void load(int id) throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(loadQuery);
            psta.setInt(1, id);
            ResultSet rset = psta.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (rset.getInt(ID) != id) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            setId(id);
            setNom(rset.getString(NOM));
            Semestre sem = new SemestreJDBC();
            sem.load(rset.getInt(SEMESTRE));
            setSemestre(sem);
            setNombreEtudiants(rset.getInt(NOMBREETUDIANTS));
            rset.close();
            psta.close();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }
    }

    public static ArrayList<ElementListe> selectSemestre(int idSemestre) throws ExceptionMetier {
        try {
            ArrayList<ElementListe> list = new ArrayList<ElementListe>();
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(selectQuerySemestre);
            psta.setInt(1, idSemestre);
            ResultSet rset = psta.executeQuery();
            while (rset.next()) {
                Semestre sem = new SemestreJDBC("",null,null,null);
                sem.load(rset.getInt(SEMESTRE));
                String nom = rset.getString(NOM);
                int nbe = rset.getInt(NOMBREETUDIANTS);
                int id = rset.getInt(ID);
                String nomSemestre = sem.getNom();

                ElementListe e = new ElementListe(id, nom + ", "+ nbe +", " +  nomSemestre);
                list.add(e);
            }
            rset.close();
            psta.close();
            return list;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }
}
